﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Objects;
using System.Data.SqlClient;

namespace _02._10.EntityFramework
{
    public static class NorthwindDAL
    {
        public static void InsertCustomer(Customer customer)
        {
            InsertCustomer(customer, 0);
        }

        private static Customer InsertCustomer(Customer customer, int thrownExceptionsCount)
        {
            try
            {
                using (NorthwindEntities context = new NorthwindEntities())
                {
                    List<string> customerIds = context.Customers.Select(c => c.CustomerID).ToList();

                    while (true)
                    {
                        string customerId = GenerateCustomerId();
                        if (!customerIds.Contains(customerId))
                        {
                            customer.CustomerID = customerId;
                            break;
                        }
                    }

                    context.Customers.AddObject(customer);
                    context.SaveChanges();

                    context.Detach(customer);

                    return customer;
                }
            }
            catch (UpdateException ex)
            {
                // this will occur if meanwhile somebody else inserted a customer with the same customerId

                if (thrownExceptionsCount > 5)
                {
                    throw;
                }
                else
                {
                    //try again
                    return InsertCustomer(customer, thrownExceptionsCount + 1);
                }
            }
        }

        private static Random random = new Random();

        private static readonly char[] chars = new char[] 
            {
                'q', 'w', 'e', 'r', 't', 'y', 'u', 'i', 'o', 'p', 'a', 's', 'd', 'f', 'g', 'h', 'j', 'k', 'l', 'z', 'x', 'c', 'v', 'b', 'n', 'm',
                'Q', 'W', 'E', 'R', 'T', 'Y', 'U', 'I', 'O', 'P', 'A', 'S', 'D', 'F', 'G', 'H', 'J', 'K', 'L', 'Z', 'X', 'C', 'V', 'B', 'N', 'M',
                '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 
            };

        private static string GenerateCustomerId()
        {            
            StringBuilder sBuilder = new StringBuilder();

            for (int i = 0; i < 5; i++)
            {
                int randomIndex = random.Next(0, chars.Length);

                sBuilder.Append(chars[randomIndex]);
            }

            return sBuilder.ToString();
        }

        public static Customer GetCustomerByContactName(string contactName)
        {
            using (NorthwindEntities context = new NorthwindEntities())
            {
                Customer customer = context.Customers.FirstOrDefault(c => c.ContactName == contactName);                
                return customer;
            }
        }

        public static void UpdateCustomer(Customer customer)
        {
            using (NorthwindEntities context = new NorthwindEntities())
            {
                context.Customers.Attach(customer);

                context.ObjectStateManager.GetObjectStateEntry(customer).SetModified();
                context.Refresh(RefreshMode.ClientWins, customer);

                context.SaveChanges();
            }
        }

        public static void DeleteCustomer(Customer customer)
        {
            using (NorthwindEntities context = new NorthwindEntities())
            {
                context.Customers.Attach(customer);                
                context.Customers.DeleteObject(customer);
                context.SaveChanges();
            }
        }

        public static IEnumerable<Customer> GetCustomersWithOrdersInYearAndCountry(int year, string country)
        {
            using (NorthwindEntities context = new NorthwindEntities())
            {
                var customers = from c in context.Customers
                          join o in context.Orders on c.CustomerID equals o.CustomerID
                          where o.OrderDate.HasValue &&
                            o.OrderDate.Value.Year == year &&
                            o.ShipCountry == country
                          select c;

                List<Customer> customersList = customers.Distinct().ToList();

                return customersList;
            }
        }

        public static IEnumerable<Customer> GetCustomersWithOrdersInYearAndCountry_Using_ObjectContext(int year, string country)
        {
            using (NorthwindEntities context = new NorthwindEntities())
            {                
                string sqlCommandText = 
                    "select * " + 
                    "from Customers " + 
                    "where CustomerID in" +
                    "(select c.CustomerID " + 
                    "from Customers c " +
                    "join Orders o on c.CustomerID like o.CustomerID " +
                    "where o.OrderDate is not null " + 
                    "and YEAR(o.OrderDate) = @year " +
                    "and o.ShipCountry like @country)";

                SqlParameter yearParameter = new SqlParameter("@year", year);
                SqlParameter countryParameter = new SqlParameter("@country", country);

                ObjectResult<Customer> customers = context.ExecuteStoreQuery<Customer>(sqlCommandText, yearParameter, countryParameter);

                List<Customer> customersList = customers.ToList();
                List<Customer> customersDistinctList = customersList.Distinct().ToList();

                return customersDistinctList;
            }            
        }
    }
}
